﻿using Cxx.Entity.Extend;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace Cxx.Common
{
    /// <summary>
    /// SQL语句过滤器
    /// </summary>
    public abstract class SqlFilter
    {
        /// <summary>
        /// 根据查询条件和字段对照表,生成SQL筛选语句
        /// </summary>
        /// <param name="condition">查询条件</param>
        /// <param name="fields">字段对照表</param>
        /// <returns>SQL筛选语句</returns>
        public static string Build(string condition, Hashtable fields)
        {
            string filters = "";
            condition = condition.Trim('|');
            //if (!string.IsNullOrEmpty(condition))
            if (!Validate(condition))
            {
                return filters;
            }
            // 去除查询条件的头尾分界符"{"和"}"
            //condition = condition.Substring(1, condition.Length - 2);
            condition = condition.Replace("{", "").Replace("}", "");

            // 将查询条件拼接成与表对应的查询字符串
            string[] terms = condition.Split('|');
            for (int i = 0; i < terms.Length; i++)
            {
                string[] term = terms[i].Split('=');
                if (!string.IsNullOrEmpty(term[1]) && fields.Contains(term[0]))
                {
                    var field = (DataFieldInfo)fields[term[0]];
                    var filter = field.Name + " " + field.Operate + " ";
                    switch (field.Type)
                    {
                        case "Integer":
                            filter += term[1];
                            break;
                        case "Character":
                            if (field.Operate == "LIKE")
                            {
                                filter += "'%" + term[1] + "%'";
                            }
                            else
                            {
                                filter += "'" + term[1] + "'";
                            }
                            break;
                        case "DateTime":
                            //if (field.Operate == "<=")
                            //{
                            //    filter += "'" + Convert.ToDateTime(term[1]).AddDays(1).ToString("yyyy-MM-dd") + "'";
                            //}
                            //else
                            //{
                                filter += "'" + term[1] + "'";
                            //}
                            break;
                        case "DateToInt":
                            filter += DateTimeUtil.DateToInt(term[1]);
                            break;
                        case "BigInt":
                            filter += term[1];
                            break;
                    }
                    filters += filter + " AND ";
                }
            }
            string filtersTemp = filters.TrimEnd();
            if (filtersTemp.EndsWith("AND"))
            {
                filters = filtersTemp.Remove(filtersTemp.Length - 3, 3);
            }

            return filters;
        }

        /// <summary>
        /// 验证查询字符串是否合法
        /// </summary>
        /// <param name="search">查询字符串</param>
        /// <returns>true/false</returns>
        public static bool Validate(string querystring)
        {
            if (string.IsNullOrEmpty(querystring))
                return false;

            Regex regex = new Regex(@"^{.+}$");
            if (!regex.IsMatch(querystring))
                return false;
            querystring = querystring.Replace("{", "").Replace("}", "");
            string[] parms = querystring.Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries);
            regex = new Regex(@"^\w+=.*$");
            foreach (string parm in parms)
            {
                if (!regex.IsMatch(parm))
                    return false;
            }
            return true;
        }
    }
}
